-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:更新视图，触发trigger,after,instead of

--step1:建表;expect:成功
drop table if exists table_src_0008_1 cascade;
drop table if exists table_des_0008_2 cascade;
drop sequence if exists test_trigger_des_seq_0008 cascade;
create table table_src_0008_1(id1 int, id2 int, id3 int) with (storage_type=ustore)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
create table table_des_0008_2(id1 int, id2 int, id3 int) with (storage_type=ustore)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
--step2：创建视图，函数，触发器，expect:成功
create or replace view test_trigger_src_view_0008 as select * from table_src_0008_1;
create sequence test_trigger_des_seq_0008;
create or replace function tri_insert_func_0008_1() returns trigger as
$$
declare
begin
insert into table_des_0008_2 values(nextval('test_trigger_des_seq_0008'),nextval('test_trigger_des_seq_0008'),nextval('test_trigger_des_seq_0008'));
return new;
end
$$ language plpgsql;
/
drop trigger if exists update_trigger_0008_1 on test_trigger_src_view_0008;
create trigger update_trigger_0008_1 after update of id1 on test_trigger_src_view_0008
for each statement
execute procedure tri_insert_func_0008_1();
/
create or replace function tri_update_func_0008_2()
returns trigger as $$
begin
update table_src_0008_1 set id3=new.id3;
return new;
end;
$$language plpgsql;
/
drop trigger if exists update_trigger_0008_2 on test_trigger_src_view_0008;
create trigger update_trigger_0008_2
instead of update on test_trigger_src_view_0008
for each row
execute procedure tri_update_func_0008_2();
/
--step3:更新视图，触发trigger,查询表;expect:成功
insert into table_src_0008_1 values(1,1,1),(2,2,2),(3,3,3);
select * from table_src_0008_1 order by id1;
select * from table_des_0008_2 order by id1;
update test_trigger_src_view_0008 set id3=100;
select * from table_src_0008_1 order by id1;
select * from table_des_0008_2 order by id1;
update test_trigger_src_view_0008 set id1=100;
select * from table_src_0008_1 order by id1;
select * from table_des_0008_2 order by id1;

--step4:环境清理;expect:成功
drop trigger update_trigger_0008_1 on test_trigger_src_view_0008 cascade;
drop trigger update_trigger_0008_2 on test_trigger_src_view_0008 cascade;
drop view test_trigger_src_view_0008;
drop table table_src_0008_1 cascade;
drop table table_des_0008_2 cascade;
drop sequence test_trigger_des_seq_0008 cascade;
drop function tri_insert_func_0008_1;
drop function tri_update_func_0008_2;